package com.mitac.mcd.cs.mobiletracking.socketserver.util;

import java.beans.PropertyVetoException;
import java.io.PrintStream;
import java.lang.reflect.Array;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeoutException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.mitac.mcd.cs.mobiletracking.socketserver.HealthCheck;
import com.mitac.mcd.cs.mobiletracking.socketserver.MobileServer;
import com.mitac.mcd.cs.mobiletracking.socketserver.MobileServerThread;
import com.mitac.mcd.cs.mobiletracking.socketserver.bean.NotificationAcceptableIpaddress;
import com.mitac.mcd.cs.mobiletracking.socketserver.bean.NotificationMarkBean;

import net.rubyeye.xmemcached.exception.MemcachedException;
import net.sf.json.JSONObject;

public class BusinessUtil
{
	public static Log sLogger = LogFactory.getLog("system");
	public static Log aLogger = LogFactory.getLog("application"); 
	
    /**
    * Description: Array add length
    * @param oldArray
    * @param addLength
    * @return Object
    */
    public static Object arrayAddLength(Object oldArray,int addLength) {
        Class<? extends Object> c = oldArray.getClass();
        if(!c.isArray())return null;
        Class<?> componentType = c.getComponentType();
        int length = Array.getLength(oldArray);
        int newLength = length + addLength;
        Object newArray = Array.newInstance(componentType,newLength);
        System.arraycopy(oldArray,0,newArray,0,length);
        return newArray;
    }
    
    /**
     * 获取传递日期所在周的周一
     * @param date
     */
    public static void getFirstDayOfWeek(Calendar date){
    	int day_of_week = date.get(Calendar.DAY_OF_WEEK);
    	if(day_of_week!=1){
    		day_of_week = day_of_week -2;
    	}else{
    		day_of_week = 6;
    	}
    	date.add(Calendar.DATE, -day_of_week);
    }
    
    /**
     * 获取传递日期所在周的周日
     * @param date
     */
    public static void getLastDayOfWeek(Calendar date){
		int day_of_week = date.get(Calendar.DAY_OF_WEEK);
		if(day_of_week!=1){
			day_of_week = date.get(Calendar.DAY_OF_WEEK) - 2;
			date.add(Calendar.DATE, -day_of_week);
			date.add(Calendar.DATE, 6);
		}
    }
    
    /**
     * 获取传递日期所在月的第一天
     * @param date
     */
    public static Date getFirstDayOfMonth(Date date){
    	Calendar calendar = new GregorianCalendar();
    	calendar.setTime(date);
    	calendar.set(Calendar.DATE,calendar.getActualMinimum(Calendar.DATE));
    	return calendar.getTime();
    }
    
    /**
     * 获取传递日期所在月的最后一天
     * @param date
     */
    public static Date getLastDayOfMonth(Date date){
    	Calendar calendar = new GregorianCalendar();
    	calendar.setTime(date);
    	calendar.set(Calendar.DATE,calendar.getActualMaximum(Calendar.DATE));
    	return calendar.getTime();
    }
    
    /**
     * @param <T> 数组中的元素类型
     * @param arrs 需要删除元素的数组。
     * @param index 需要删除的元素的索引（出界时抛异常）。
     * @return 指定类型的新数组。
     */
    public static <T> T[] removeArrayItem(T[] arrs, int index) {
        int len = arrs.length;
        if(index < 0 || index >= len) {
            throw new IllegalArgumentException("索引出界");
        }
        List<T> list = new LinkedList<T>();
        for(int i = 0; i < len; i++) {
            if(i != index) {
                list.add(arrs[i]);
            }
        }
        // 这里将改变传入的数组参数 arrs 的值
        arrs = list.toArray(arrs);
        return java.util.Arrays.copyOf(arrs, arrs.length - 1);
    }
    
    /**
     * 向noteList中的uuid对应的client发送指定的讯息
     * @param noteList
     * @param user_id
     * @param device_type
     * @param client_address
     */
    public static void sendNotification(String ID,List<Map> noteList,int type,int user_id,int device_type,String client_address,boolean newest){
    	if (noteList==null||noteList.size()==0){
    		BusinessUtil.aLogger.info("noteList为空，无法向client发送notification讯息...");
    		return;
    	}
    	int clint_count = 0;
    	for (Map map:noteList){
    		String uuid = (String) map.get("uid");
    		int badge = Integer.parseInt(map.get("badge").toString());
    		boolean flag = sendNotificationToClient( uuid, ID, badge, type, user_id, device_type, client_address);
    		if (flag){//success
    			new CheckReNotification(uuid,ID,badge,type,user_id, device_type, client_address);
    			//log notification detail to db
    			BusinessUtil.logNotificationDetail(ID, uuid, null, -1, "WAIT");
    		}
    		clint_count++;
    	}
    	BusinessUtil.logNotification(ID, true, type,client_address, clint_count, null,newest);
    }
    
    /**
     * 向指定uuid的client发送指定的讯息
     * @param uuid
     * @param ID
     * @param badge
     * @param type
     * @param user_id
     * @param device_type
     * @param client_address
     * @return
     */
    public static boolean sendNotificationToClient(String uuid,String ID,int badge,int type,int user_id,int device_type,String client_address){
    	boolean flag = true;
    	if (MobileServer.outputStreams.containsKey(uuid)){
			PrintStream ps = MobileServer.outputStreams.get(uuid);
			Map newmap = new HashMap();
			newmap.put("cmd", "notification");
			newmap.put("ID", ID);
			newmap.put("badge", badge);
			newmap.put("type", type);
			JSONObject json = JSONObject.fromObject(newmap);
			try{
				//向client端发送讯息
				ps.println(json.toString());
				BusinessUtil.aLogger.info("完成向["+uuid+"]发送notification讯息:"+json.toString());
			}catch(Exception ex){
				String note = "失败向["+uuid+"]发送notification讯息:"+ex;
				flag = false;
				BusinessUtil.logException(uuid, user_id, device_type, client_address, "E013",note);
				BusinessUtil.aLogger.error(note);
			}
			//设置发送成功的标记
			if (flag){
				if (MobileServer.healthCheckList.containsKey(uuid)){
					HealthCheck _hc = MobileServer.healthCheckList.get(uuid);
					List<NotificationMarkBean> notificationList = _hc.getNotificationList();
					boolean isExist = false;
					for(NotificationMarkBean bean:notificationList){
						if (bean.getId().equals(ID)) {
							isExist = true;
							bean.setSendFlag(true);
							bean.setSendDate(new Date());
							bean.setSendTime(1);
						}
					}
					if (!isExist){
						NotificationMarkBean bean = new NotificationMarkBean();
						bean.setId(ID);
						bean.setSendFlag(true);
						bean.setSendDate(new Date());
						bean.setSendTime(1);
						notificationList.add(bean);
					}
					BusinessUtil.aLogger.info("完成在healthCheckList中添加设置send notification标志...");
				}
			}
		}else{
			flag = false;
			String note = uuid+" client系统记录为不在线，无法向client发送notification讯息...";
			BusinessUtil.logException(uuid, user_id, device_type, client_address, "E012",note);
			BusinessUtil.logNotificationDetail(ID, uuid, "E012", -1, "FAIL");
			BusinessUtil.aLogger.info(note);
		}
    	return flag;
    }
    
    /**
     * 判断字符串中cmd是属于那种类型，并返回该类型的枚举
     * @param s
     * @return
     */
    public static ClientCMDEnum.CMD judge(JSONObject jsonObject){

        ClientCMDEnum.CMD flag = null;
        try {
            if (jsonObject.get("cmd").equals("aut")){
                flag = ClientCMDEnum.CMD.aut;
            }else if (jsonObject.get("cmd").equals("gpsupload")){
                flag = ClientCMDEnum.CMD.gpsupload;
            }else if (jsonObject.get("cmd").equals("notification")){
                flag = ClientCMDEnum.CMD.notification;
            }else if (jsonObject.get("cmd").equals("notificationcmd")){
                flag = ClientCMDEnum.CMD.notificationcmd;
            }else if (jsonObject.get("cmd").equals("renotification")){
                flag = ClientCMDEnum.CMD.renotification;
            }else if (jsonObject.get("cmd").equals("bye")){
                flag = ClientCMDEnum.CMD.bye;
            }else if (jsonObject.get("cmd").equals("serverstatus")){
                flag = ClientCMDEnum.CMD.serverstatus;
            }else if (jsonObject.get("cmd").equals("serverconfig")){
                flag = ClientCMDEnum.CMD.serverconfig;
            }else if (jsonObject.get("cmd").equals("transnotificationcmd")){
                flag = ClientCMDEnum.CMD.transnotificationcmd;
            }else if (jsonObject.get("cmd").equals("transgroupupdatecmd")){
                flag = ClientCMDEnum.CMD.transgroupupdatecmd;
            }else if (jsonObject.get("cmd").equals("transkillcmd")){
                flag = ClientCMDEnum.CMD.transkillcmd;
            }
            
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return flag;
    }
    
    /**
     * 先讀出 memcache 中 UUID key 的值. 將之 update 於 table A,B.
     * 取出最後一筆 buffer, 把他存入或新建 LastUxxxxxx  的key 內. LastUxxxxxx 為紀錄每個 UUID 最後位置的地方.
     * 將這最後一筆 buffer 存入 Table C.  Table C 存放每個 UUID 最後位置
     * 抹掉 Uxxxxxxx key 
	 * 把 GroupID key 中的 UUID 值移除
	 * 把TaskID key中的UUID 值移除
	 * 在db中移除online client的讯息
     * 移除相關此 socket thread 中暫存資料, ex: outputstream object hash table 內容資料
     * @param uuid
     * @param user_id
     * @param deviceType
     * @param groupId
     */
    public static void afterCloseSocketDone(MobileServerThread threadObj){
    	if (threadObj.uuid==null||threadObj.uuid.equals("")){
            return;
        }
    	String tag = threadObj.mode.equals(DeviceConnectMode.beTracked)?"U"+threadObj.uuid:threadObj.user_id+"_"+threadObj.uuid;
    	Calendar startdate = Calendar.getInstance();
    	BusinessUtil.aLogger.info("UUID:"+threadObj.uuid+" begin afterCloseSocketDone,Time:"+Formatter.ms_sdf.format(startdate.getTime()));
        String temp = null;
		try {
			temp = (String)MemcacheUtil.getMemCachedClient().get(tag);
		} catch (TimeoutException e1) {
			String note = "function afterCloseSocketDone中访问memcache获取key为"+tag+"的value失败....."+e1;
			BusinessUtil.aLogger.error(note);
			BusinessUtil.logException(threadObj.uuid, threadObj.user_id, threadObj.deviceType, threadObj.clientAddress, "E011",note);
		} catch (InterruptedException e1) {
			e1.printStackTrace();
		} catch (MemcachedException e1) {
			String note = "function afterCloseSocketDone中访问memcache获取key为"+tag+"的value失败....."+e1;
			BusinessUtil.aLogger.error(note);
			BusinessUtil.logException(threadObj.uuid, threadObj.user_id, threadObj.deviceType, threadObj.clientAddress, "E011",note);
		}
        BusinessUtil.aLogger.info("在memcache中取出"+tag+"的值为："+temp);
        String[] result = StringToArray(temp,",");
        String lastTag = threadObj.user_id==0?"Last"+"U"+threadObj.uuid:"Last"+"USR"+threadObj.user_id;
        if (result!=null){
        	BusinessUtil.aLogger.info("开始将"+tag+"的值update到DB的A，B表中...");
        	BusinessUtil.saveDB(result, threadObj.uuid, threadObj.user_id, threadObj.deviceType,threadObj.clientAddress);
        	BusinessUtil.aLogger.info("完成"+tag+"的值update到DB的A，B表中...");
        	int index = Integer.parseInt(result[0]);
        	String lastKnownPosition = result[index];
        	BusinessUtil.aLogger.info(tag+"的Last Known Position:"+lastKnownPosition);
        	boolean flag = false;
			try {
				flag = MemcacheUtil.getMemCachedClient().set(lastTag,0,lastKnownPosition);
			} catch (TimeoutException e1) {
				String note = "function afterCloseSocketDone中set key为"+lastTag+"的item失败....."+e1;
				BusinessUtil.aLogger.error(note);
				BusinessUtil.logException(threadObj.uuid, threadObj.user_id, threadObj.deviceType, threadObj.clientAddress, "E011",note);
			} catch (InterruptedException e1) {
				e1.printStackTrace();
			} catch (MemcachedException e1) {
				String note = "function afterCloseSocketDone中set key为"+lastTag+"的item失败....."+e1;
				BusinessUtil.aLogger.error(note);
				BusinessUtil.logException(threadObj.uuid, threadObj.user_id, threadObj.deviceType, threadObj.clientAddress, "E011",note);
			}
        	if (!flag){//访问memcache失败
        		//log to db
        		String note = "失败:"+tag+"的Last Known Position更新到memcache中...";
    			BusinessUtil.logException(threadObj.uuid, threadObj.user_id, threadObj.deviceType, threadObj.clientAddress, "E011",note);
    			BusinessUtil.aLogger.info(note);
        	}else{
        		BusinessUtil.aLogger.info(tag+"的Last Known Position更新到memcache中...");
        	}
        	try {
				saveLastKnownPosition(threadObj,lastKnownPosition);
			} catch (ParseException e) {
				e.printStackTrace();
			}
        	BusinessUtil.aLogger.info("完成"+tag+"的Last Known Position存入到DB的table C中...");
        	boolean delflag = false;
			try {
				delflag = MemcacheUtil.getMemCachedClient().delete(tag);
			} catch (TimeoutException e) {
				String note = "function afterCloseSocketDone中delete key为"+lastTag+"的item失败....."+e;
				BusinessUtil.aLogger.error(note);
				BusinessUtil.logException(threadObj.uuid, threadObj.user_id, threadObj.deviceType, threadObj.clientAddress, "E011",note);
			} catch (InterruptedException e) {
				e.printStackTrace();
			} catch (MemcachedException e) {
				String note = "function afterCloseSocketDone中delete key为"+lastTag+"的item失败....."+e;
				BusinessUtil.aLogger.error(note);
				BusinessUtil.logException(threadObj.uuid, threadObj.user_id, threadObj.deviceType, threadObj.clientAddress, "E011",note);
			}
        	if (!delflag){//访问memcache失败
        		String note = "失败:"+"在memcache中删除key为"+tag+"的item...";
        		//log to db
    			BusinessUtil.logException(threadObj.uuid, threadObj.user_id, threadObj.deviceType, threadObj.clientAddress, "E011",note);
    			BusinessUtil.aLogger.info(note);
        	}else{
        		BusinessUtil.aLogger.info("完成在memcache中删除key为"+tag+"的item...");
        	}
        }
        threadObj.removeDeviceFromGroup(threadObj.uuid,threadObj.user_id,threadObj.groupId,threadObj.mode);
    	BusinessUtil.aLogger.info("完成在memcache groupId中删除value中包含的"+threadObj.uuid+"...");
    	threadObj.removeDeviceFromTask(threadObj.uuid,threadObj.user_id,threadObj.taskId,threadObj.mode);
    	BusinessUtil.aLogger.info("完成在memcache taskId中删除value中包含的"+threadObj.uuid+"...");
    	//BusinessUtil.removeOnlineClient(threadObj.uuid);
    	//BusinessUtil.aLogger.info("完成在db onlien client table中删除client:"+threadObj.uuid+"...");
    	BusinessUtil.removeConnection(threadObj.uuid);
    	BusinessUtil.aLogger.info("完成在outputStreams中删除key为"+threadObj.uuid+"的outputstream item...");
    	BusinessUtil.removeHealthCheck(threadObj.uuid);
    	BusinessUtil.aLogger.info("完成在healthCheckList中删除key为"+threadObj.uuid+"的healthcheck object...");
    	BusinessUtil.removeOnlineClient(threadObj.uuid,threadObj.user_id);
    	BusinessUtil.aLogger.info("完成在db中删除key为"+threadObj.uuid+"的online device讯息...");
    	if(MobileServer.uuidUseridList.containsKey(threadObj.uuid)){
        	MobileServer.uuidUseridList.remove(threadObj.uuid);
        }
    	BusinessUtil.aLogger.info("完成在uuidUseridList中删除key为"+threadObj.uuid+"的item...");
        if(MobileServer.useridUuidList.containsKey(threadObj.user_id)){
        	MobileServer.useridUuidList.remove(threadObj.user_id);
        }
        BusinessUtil.aLogger.info("完成在useridUuidList中删除key为"+threadObj.user_id+"的item...");
        if(MobileServer.uuidGroupList.containsKey(threadObj.uuid)){
        	MobileServer.uuidGroupList.remove(threadObj.uuid);
        }
    	BusinessUtil.aLogger.info("完成在uuidGroupList中删除key为"+threadObj.uuid+"的item...");
    	if(MobileServer.uuidTaskList.containsKey(threadObj.uuid)){
        	MobileServer.uuidTaskList.remove(threadObj.uuid);
        }
    	BusinessUtil.aLogger.info("完成在uuidTaskList中删除key为"+threadObj.uuid+"的item...");
    	Calendar finishdate = Calendar.getInstance();
    	BusinessUtil.aLogger.info("UUID:"+threadObj.uuid+" finish afterCloseSocketDone,Time:"+Formatter.ms_sdf.format(finishdate.getTime()));
    	long difftime = finishdate.getTime().getTime() - startdate.getTime().getTime();
    	BusinessUtil.aLogger.info("UUID:"+threadObj.uuid+" 本次afterCloseSocketDone耗时："+difftime+"ms");
    	//set flag to true
    	threadObj.isExeCloseSocketDone = true;
    	BusinessUtil.logCloseSocket(startdate, finishdate, threadObj.uuid, threadObj.user_id, MobileServer.IP,MobileServer.PORT, null);
    }
    
    /**
     * 將這最後一筆 buffer 存入 Table C.  Table C 存放每個 UUID 最後位置
     * @param uuid
     * @param lastKnownPosition
     * @throws ParseException 
     */
    public static void saveLastKnownPosition(MobileServerThread obj,String lastKnownPosition) throws ParseException{
    	if (null==lastKnownPosition||lastKnownPosition.equals("")){
    		BusinessUtil.aLogger.info("saveLastKnownPosition失败，lastKnownPosition=="+lastKnownPosition);
    		return;
    	}
    	String [] temp = BusinessUtil.StringToArray(lastKnownPosition, "#");
    	double lat = Double.parseDouble(temp[0]);
    	double lon = Double.parseDouble(temp[1]);
    	double height = Double.parseDouble(temp[2]);
    	double speed = Double.parseDouble(temp[3]);
    	String date = temp[4];
    	
    	java.sql.Connection dbc=null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        boolean flag = false;
    	if (obj.mode.equals(DeviceConnectMode.beTracked)){//be tracked mode
    		try {
            	String sqlA = " select 0 from GPS_LATEST_KNOWN_POSITION where UUID = '"+obj.uuid+"' ";
                dbc = DBConnection.getConnection("gpsdata");
    			pst = dbc.prepareStatement(sqlA);
    			rs = pst.executeQuery();
    			rs.last();
    	        int intRowCount = rs.getRow();
    	        if (intRowCount>0){
    	        	flag = true;
    	        }
    	        String sqlB = null;
    	        if (flag){//update
    	        	sqlB = " update GPS_LATEST_KNOWN_POSITION set LAT=?,LON=?,HEIGHT=?,SPEED=?,LASTKNOWNTIME=?,USERID_REF=?,DEVICE_TYPE=? where UUID = ? ";
    	        	pst = dbc.prepareStatement(sqlB);
    	        	pst.setDouble(1, lat);
    	        	pst.setDouble(2, lon);
    	        	pst.setDouble(3, height);
    	        	pst.setDouble(4, speed);
    	        	pst.setTimestamp(5, new java.sql.Timestamp(Formatter.sdf.parse(date).getTime()));
    	        	pst.setInt(6, obj.ref_userId);
    	        	pst.setInt(7, obj.deviceType);
    	        	pst.setString(8, obj.uuid);
    			}else{//insert
    				sqlB = " insert into GPS_LATEST_KNOWN_POSITION (UUID,LAT,LON,HEIGHT,SPEED,LASTKNOWNTIME,USERID_REF,DEVICE_TYPE) values (?,?,?,?,?,?,?,?) ";
    				pst = dbc.prepareStatement(sqlB);
    	        	pst.setString(1, obj.uuid);
    	        	pst.setDouble(2, lat);
    	        	pst.setDouble(3, lon);
    	        	pst.setDouble(4, height);
    	        	pst.setDouble(5, speed);
    	        	pst.setTimestamp(6, new java.sql.Timestamp(Formatter.sdf.parse(date).getTime()));
    	        	pst.setInt(7, obj.ref_userId);
    	        	pst.setInt(8, obj.deviceType);
    			}
    	        pst.executeUpdate();
    		} catch (SQLException e2) {
    			BusinessUtil.aLogger.error("saveLastKnownPosition:"+e2);
    			BusinessUtil.logException(obj.uuid, obj.user_id, obj.deviceType, obj.clientAddress, "E005",""+e2);
    		} catch (PropertyVetoException e) {
    			e.printStackTrace();
    		}
    	}else{//login mode
    		if(MobileServer.enableMultiLoginMode){//包含两种情况
    			
    		}else{//userId
    			try {
                	String sqlA = " select 0 from GPS_LATEST_KNOWN_POSITION where USER_ID = "+obj.user_id;
                    dbc = DBConnection.getConnection("gpsdata");
        			pst = dbc.prepareStatement(sqlA);
        			rs = pst.executeQuery();
        			rs.last();
        	        int intRowCount = rs.getRow();
        	        if (intRowCount>0){
        	        	flag = true;
        	        }
        	        String sqlB = null;
        	        if (flag){//update
        	        	sqlB = " update GPS_LATEST_KNOWN_POSITION set LAT=?,LON=?,HEIGHT=?,SPEED=?,LASTKNOWNTIME=?,UUID_REF=?,DEVICE_TYPE=? where USER_ID = ? ";
        	        	pst = dbc.prepareStatement(sqlB);
        	        	pst.setDouble(1, lat);
        	        	pst.setDouble(2, lon);
        	        	pst.setDouble(3, height);
        	        	pst.setDouble(4, speed);
        	        	pst.setTimestamp(5, new java.sql.Timestamp(Formatter.sdf.parse(date).getTime()));
        	        	pst.setString(6, obj.ref_uuid);
        	        	pst.setInt(7, obj.deviceType);
        	        	pst.setInt(8, obj.user_id);
        			}else{//insert
        				sqlB = " insert into GPS_LATEST_KNOWN_POSITION (UUID_REF,LAT,LON,HEIGHT,SPEED,LASTKNOWNTIME,USER_ID,DEVICE_TYPE) values (?,?,?,?,?,?,?,?) ";
        				pst = dbc.prepareStatement(sqlB);
        	        	pst.setString(1, obj.ref_uuid);
        	        	pst.setDouble(2, lat);
        	        	pst.setDouble(3, lon);
        	        	pst.setDouble(4, height);
        	        	pst.setDouble(5, speed);
        	        	pst.setTimestamp(6, new java.sql.Timestamp(Formatter.sdf.parse(date).getTime()));
        	        	pst.setInt(7, obj.user_id);
        	        	pst.setInt(8, obj.deviceType);
        			}
        	        pst.executeUpdate();
        		} catch (SQLException e2) {
        			BusinessUtil.aLogger.error("saveLastKnownPosition:"+e2);
        			BusinessUtil.logException(obj.uuid, obj.user_id, obj.deviceType, obj.clientAddress, "E005",""+e2);
        		} catch (PropertyVetoException e) {
        			e.printStackTrace();
        		}
    		}
    	}
    	
		try
        {
			if(null!=rs) rs.close();
            dbc.close();
            pst.close();
        }catch(SQLException e)
        {
            e.printStackTrace();
        }
    }
    
    /**
     * 记录异常信息到DB当中
     * @param uid
     * @param user_id
     * @param device_type
     * @param client_address
     * @param exception_prop_code
     * @param note
     */
    public static void logException(String uid,int user_id,int device_type,String client_address,String exception_prop_code,String note){
    	if (null==exception_prop_code||exception_prop_code.equals("")){
    		BusinessUtil.aLogger.info("exception_prop_code为空，不能记录异常信息...");
            return;
        }
    	java.sql.Connection dbc=null;
    	PreparedStatement pst = null;
    	
        String sqlA = " insert into EXCEPTION_LOG " +
        		"(UUID,USER_ID,DEVICE_TYPE,EXCEPTION_PROP_CODE,CLIENT_IPADDRESS,RECORD_TIME,NOTE) values " +
        		"(?,?,?,?,?,?,?) ";
        try {
            dbc = DBConnection.getConnection("gpsdata");
			pst = dbc.prepareStatement(sqlA);
			pst.setString(1, uid);
			pst.setInt(2, user_id);
			pst.setInt(3, device_type);
			pst.setString(4, exception_prop_code);
			pst.setString(5, client_address);
			Date date = new Date();
			pst.setTimestamp(6, new java.sql.Timestamp(date.getTime()));
			pst.setString(7, note);
			pst.executeUpdate();
			
			dbc.close();
            pst.close();
		} catch (SQLException e) {
			BusinessUtil.aLogger.error("logException:"+e);
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}
		
    }
    
    /**
     * log notification info to db
     * @param ID
     * @param isValidate
     * @param ipAddress
     * @param clientCount
     * @param note
     */
    public static void logNotification(String ID,boolean isValidate,int type,String ipAddress,int clientCount,String note,boolean newest){
    	java.sql.Connection dbc=null;
    	PreparedStatement pst = null;
    	
        String sqlA = " insert into NOTIFICATION_LOG " +
        		"(NOTIFICATION_ID,IS_VALIDATE,IPADDRESS,CLIENT_COUNT,LOG_TIME,NOTE,NEWEST,TYPE) values " +
        		"(?,?,?,?,?,?,?,?) ";
        try {
            dbc = DBConnection.getConnection("gpsdata");
			pst = dbc.prepareStatement(sqlA);
			pst.setString(1, ID);
			pst.setBoolean(2,isValidate);
			pst.setString(3, ipAddress);
			pst.setInt(4, clientCount);
			Date date = new Date();
			pst.setTimestamp(5, new java.sql.Timestamp(date.getTime()));
			pst.setString(6, note);
			pst.setBoolean(7, newest);
			pst.setInt(8, type);
			pst.executeUpdate();
			
			dbc.close();
            pst.close();
		} catch (SQLException e) {
			BusinessUtil.aLogger.error("logNotification:"+e);
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}
    }
    
    /**
     * log system start time to db and return log sn
     * @return
     */
    public static void logServer(int SERVER_ID,String server_sn){
    	java.sql.Connection dbc=null;
    	PreparedStatement pst = null;
        String sqlA = " insert into MOBILE_SERVER_LOG " +
        		"(SERVER_ID,SYSTEM_SN,SYSTEM_START_TIME,SYSTEM_STOP_TIME,NOTE) values " +
        		"(?,?,?,?,?) ";
        try {
            dbc = DBConnection.getConnection("gpsdata");
			pst = dbc.prepareStatement(sqlA);
			pst.setInt(1, SERVER_ID);
			pst.setString(2, server_sn);
			pst.setTimestamp(3, new java.sql.Timestamp(MobileServer.starttime.getTime()));
			pst.setTimestamp(4,null);
			pst.setString(5, "Socket Server Start");
			pst.executeUpdate();
			
			dbc.close();
            pst.close();
		} catch (SQLException e) {
			BusinessUtil.aLogger.error("logServer:"+e);
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}
    }
    
    /**
     * log client login info to db
     * @param uuid
     * @param serverIP
     * @param serverPort
     * @param note
     */
    public static void logOnlineClient(String uuid,int userId,int server_id,String note){
    	java.sql.Connection dbc=null;
    	PreparedStatement pst = null;
    	ResultSet rs = null;
    	boolean insertFlag = false;
    	String sqlA = " select count(0) c from ONLINE_CLIENT where UUID = ? and USER_ID = ? " ;
        String sqlB = " insert into ONLINE_CLIENT " +
        		"(UUID,USER_ID,LOGIN_TIME,SERVER_ID,NOTE) values " +
        		"(?,?,?,?,?) ";
        String sqlC = " update ONLINE_CLIENT set LOGIN_TIME=?,NOTE=?,SERVER_ID=?" +
        		" where UUID = ? and USER_ID = ? ";
        try {
            dbc = DBConnection.getConnection("gpsdata");
			pst = dbc.prepareStatement(sqlA);
			pst.setString(1, uuid);
			pst.setInt(2, userId);
			rs = pst.executeQuery();
			int count=0;
			while(rs.next()){
				count = rs.getInt("c");
			}
			if(count==0){
				insertFlag = true;
			}
			Date date = new Date();
			if(insertFlag){//没有记录，可以insert
				pst = dbc.prepareStatement(sqlB);
				pst.setString(1, uuid);
				pst.setInt(2,userId);
				pst.setTimestamp(3, new java.sql.Timestamp(date.getTime()));
				pst.setInt(4, server_id);
				pst.setString(5, note);
				pst.executeUpdate();
			}else{//有记录，则更新
				pst = dbc.prepareStatement(sqlC);
				pst.setTimestamp(1, new java.sql.Timestamp(date.getTime()));
				pst.setString(2, note);
				pst.setInt(3, server_id);
				pst.setString(4, uuid);
				pst.setInt(5, userId);
				pst.executeUpdate();
			}

			if(null!=rs) rs.close();
            dbc.close();
            pst.close();
		} catch (SQLException e) {
			BusinessUtil.aLogger.error("logOnlineClient:"+e);
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}
    }
    
    /**
     * 删除online client table中指定uuid的记录
     * @param uuid
     */
    public static void removeOnlineClient(String uuid,int userId){
    	java.sql.Connection dbc=null;
    	PreparedStatement pst = null;
    	String sqlA = null;
    	sqlA = " delete from ONLINE_CLIENT where UUID = ? and USER_ID = ? ";
    	
        try {
            dbc = DBConnection.getConnection("gpsdata");
			pst = dbc.prepareStatement(sqlA);
			pst.setString(1, uuid);
			pst.setInt(2, userId);
			pst.executeUpdate();
			dbc.close();
            pst.close();
		} catch (SQLException e) {
			BusinessUtil.aLogger.error("removeOnlineClient:"+e);
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}
    }
    
    /**
     * if aut successfully then log time to db
     * @param startTime
     * @param finishTime
     * @param uuid
     * @param user_id
     * @param note
     */
    public static void logAut(Calendar startTime,Calendar finishTime,String uuid,int user_id,String ipaddress,String note){
    	java.sql.Connection dbc=null;
    	PreparedStatement pst = null;
        String sqlA = " insert into AUT_LOG " +
        		"(AUT_START_TIME,AUT_START_TIME_MS,AUT_FINISH_TIME,AUT_FINISH_TIME_MS,DIFF_TIME,UUID,USER_ID,IPADDRESS,NOTE) values " +
        		"(?,?,?,?,?,?,?,?,?) ";
        try {
            dbc = DBConnection.getConnection("gpsdata");
			pst = dbc.prepareStatement(sqlA);
			pst.setTimestamp(1, new java.sql.Timestamp(startTime.getTime().getTime()));
			pst.setInt(2, startTime.get(Calendar.MILLISECOND));
			pst.setTimestamp(3, new java.sql.Timestamp(finishTime.getTime().getTime()));
			pst.setInt(4, finishTime.get(Calendar.MILLISECOND));
			pst.setLong(5, finishTime.getTimeInMillis()-startTime.getTimeInMillis());
			pst.setString(6, uuid);
			pst.setInt(7, user_id);
			pst.setString(8, ipaddress);
			pst.setString(9, note);
			pst.executeUpdate();
			
			dbc.close();
            pst.close();
		} catch (SQLException e) {
			BusinessUtil.aLogger.error("logAut:"+e);
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}
    }
    
    /**
     * 记录close socket所需要花费的时间
     * @param startTime
     * @param finishTime
     * @param uuid
     * @param user_id
     * @param ipaddress
     * @param note
     */
    public static void logCloseSocket(Calendar startTime,Calendar finishTime,String uuid,int user_id,String ipaddress,int port,String note){
    	java.sql.Connection dbc=null;
    	PreparedStatement pst = null;
        String sqlA = " insert into CLOSESOCKET_LOG " +
        		"(CLOSESOCKET_START_TIME,CLOSESOCKET_START_TIME_MS,CLOSESOCKET_FINISH_TIME,CLOSESOCKET_FINISH_TIME_MS,DIFF_TIME,UUID,USER_ID,IPADDRESS,NOTE) values " +
        		"(?,?,?,?,?,?,?,?,?) ";
        try {
            dbc = DBConnection.getConnection("gpsdata");
			pst = dbc.prepareStatement(sqlA);
			pst.setTimestamp(1, new java.sql.Timestamp(startTime.getTime().getTime()));
			pst.setInt(2, startTime.get(Calendar.MILLISECOND));
			pst.setTimestamp(3, new java.sql.Timestamp(finishTime.getTime().getTime()));
			pst.setInt(4, finishTime.get(Calendar.MILLISECOND));
			pst.setLong(5, finishTime.getTimeInMillis()-startTime.getTimeInMillis());
			pst.setString(6, uuid);
			pst.setInt(7, user_id);
			pst.setString(8, ipaddress+":"+port);
			pst.setString(9, note);
			pst.executeUpdate();
			
			dbc.close();
            pst.close();
		} catch (SQLException e) {
			BusinessUtil.aLogger.error("logCloseSocket:"+e);
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}
    }
    
    /**
     * 返回执行authentication动作的平均时间，单位ms
     * @return
     */
    public static int getAverageAutTime(){
    	java.sql.Connection dbc=null;
    	PreparedStatement pst = null;
    	ResultSet rs = null;
    	int avg_time = 0;
    	String sqlA = " select avg(diff_time) as avg_time from AUT_LOG where IPADDRESS = ? " ;
        try {
            dbc = DBConnection.getConnection("gpsdata");
			pst = dbc.prepareStatement(sqlA);
			pst.setString(1, MobileServer.IP);
			rs = pst.executeQuery();
			while(rs.next()){
				avg_time = rs.getInt("avg_time");
			}
			
			if(null!=rs) rs.close();
            dbc.close();
            pst.close();
		} catch (SQLException e) {
			BusinessUtil.aLogger.error("logOnlineClient:"+e);
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}
		return avg_time;
    }
    
    /**
     * log notification detail info to db
     * @param ID
     * @param uuid
     * @param failCode
     * @param resendTime
     * @param note
     */
    public static void logNotificationDetail(String ID,String uuid,String failCode,int resendTime,String note){
    	java.sql.Connection dbc=null;
    	PreparedStatement pst = null;
    	ResultSet rs = null;
    	boolean isExist = false;
    	String sqlA = " select UUID from NOTIFICATION_DETAIL where NOTIFICATION_ID=? and UUID=? ";
    	
        String sqlB = " insert into NOTIFICATION_DETAIL " +
		"(NOTIFICATION_ID,UUID,FAIL_CODE,RESEND_TIME,LOG_TIME,NOTE) values " +
		"(?,?,?,?,?,?) ";
        
        String sqlC = " update NOTIFICATION_DETAIL set FAIL_CODE=?,RESEND_TIME=?,LOG_TIME=?,NOTE=? " +
        		"where NOTIFICATION_ID=? and UUID=? ";
        try {
            dbc = DBConnection.getConnection("gpsdata");
            
            pst = dbc.prepareStatement(sqlA);
            pst.setString(1, ID);
            pst.setString(2, uuid);
            rs = pst.executeQuery();
            while(rs.next()){
            	isExist = true;
            }
            Date date = new Date();
            if(isExist){//update
            	pst = dbc.prepareStatement(sqlC);
    			pst.setString(1, failCode);
    			pst.setInt(2, resendTime);
    			pst.setTimestamp(3, new java.sql.Timestamp(date.getTime()));
    			pst.setString(4, note);
    			pst.setString(5, ID);
    			pst.setString(6, uuid);
            }else{//insert
            	pst = dbc.prepareStatement(sqlB);
    			pst.setString(1, ID);
    			pst.setString(2, uuid);
    			pst.setString(3, failCode);
    			pst.setInt(4, resendTime);
    			pst.setTimestamp(5, new java.sql.Timestamp(date.getTime()));
    			pst.setString(6, note);
            }
			
			pst.executeUpdate();
			
			if(null!=rs) rs.close();
			dbc.close();
            pst.close();
		} catch (SQLException e) {
			BusinessUtil.aLogger.error("logNotificationDetail:"+e);
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}
    }
    
    /**
     * 将全部result中的数据写入到DB中保存为history数据，分别保存tableA和tableB
     * @param result
     * @param uuid
     * @param deviceType
     * @param speed
     * @param date
     */
	public static void saveDB(String[] result,String uuid,int user_id,int deviceType,String clientAddress)
    {
        BusinessUtil.aLogger.info("length:"+result.length);
        BusinessUtil.aLogger.info("当前item count:"+result[0]);
        java.sql.Connection dbc=null;
        if (result.length<=0){
            return;
        }
        /**
         * Table A: UUID  user_id  Lat   Lon  High  Speed  DateTime   標準的資料
           Table B: UUID  user_id  Start_Time  End_Time  DetailInfo    這是給查整天資料的情況設計的 table
                            其中 Detail info 為 Position_1#Position_2#Position3.... 
         */
        PreparedStatement pst = null;
        try
        {
            String sqlA = " insert into GPS_POSITION_RAW (UUID,USER_ID,DEVICE_TYPE,LAT,LON,HEIGHT,SPEED,UPLOADTIME) values " +
            "(?,?,?,?,?,?,?,?) ";
            dbc = DBConnection.getConnection("gpsdata");
            pst = dbc.prepareStatement(sqlA);
            for (int i=1;i<result.length;i++){
                String item = result[i];
                String [] temp = item.split("#");
                double lat = Double.parseDouble(temp[0]);
                double lon = Double.parseDouble(temp[1]);
                double high = Double.parseDouble(temp[2]);
                double speed = Double.parseDouble(temp[3]);
                String date = temp[4];
                pst.setString(1, uuid);
                pst.setInt(2, user_id);
                pst.setInt(3, deviceType);
                pst.setDouble(4, lat);
                pst.setDouble(5, lon);
                pst.setDouble(6, high);
                pst.setDouble(7, speed);
                pst.setTimestamp(8, new java.sql.Timestamp(Formatter.sdf.parse(date).getTime()));
                pst.addBatch();
            }
            pst.executeBatch();
            
            String Start_Time = null;String End_Time = null;
            StringBuffer sbC = new StringBuffer();
            for (int i=1;i<result.length;i++){
                String item = result[i];
                sbC.append(item+",");
                if (i==1){
                    String [] temp1 = item.split("#");
                    Start_Time = temp1[4];
                }
                if (i==result.length-1){
                    String [] temp1 = item.split("#");
                    End_Time = temp1[4];
                }
            }
            String sqlC = " insert into GPS_POSITION_MIX (UUID,USER_ID,DEVICE_TYPE,START_TIME,START_COORDS,END_TIME,END_COORDS,DETAILINFO) values " +
                    "('"+uuid+"','"+user_id+"',"+deviceType+","+parseDateFormat(Start_Time)+",'"+result[1]+"',"+parseDateFormat(End_Time)+",'"+result[result.length-1]+"','"+sbC.toString().substring(0,sbC.length()-1)+"') " ;
            //BusinessUtil.aLogger.info(sqlC);
            pst = dbc.prepareStatement(sqlC);
            pst.executeUpdate();
            
        }catch(SQLException e)
        {
        	BusinessUtil.aLogger.error("saveDB:"+e);
			BusinessUtil.logException(uuid, user_id, deviceType, clientAddress, "E005",""+e);
        } catch (PropertyVetoException e) {
        	BusinessUtil.aLogger.error("saveDB:"+e);
		} catch (ParseException e) {
			BusinessUtil.aLogger.error("saveDB:"+e);
		}
        
        try
        {
            dbc.close();
            pst.close();
        }catch(SQLException e)
        {
            e.printStackTrace();
        }
        
    }
	
	/**
     * 将用逗号分隔的字符串转换为数组
     * @param s
     * @return
     */
    public static String[] StringToArray(String Str,String regStr){
        if (Str==null)
            return null;
        if (Str.substring(Str.length()-1,Str.length()).equals(regStr))
            return null;
        if (Str.indexOf(regStr)<=0){
            return new String[]{Str};
        }
        return Str.split(regStr);
    }
    
    /**
     * 将数组转换为用逗号分隔的字符串
     * @param array
     * @return
     */
    public static String ArrayToString(String [] array){
        if (array==null)
            return null;
        StringBuffer returnStr = null;
        Class<? extends String[]> type = array.getClass();
        if (!type.isArray())
            return null;
        if (array.length==0)
            return null;
        returnStr = new StringBuffer();
        for (String s:array){
            returnStr.append(s+",");
        }
        return returnStr.substring(0,returnStr.length()-1);
    }
    
    /**
     * 传入格式：HH:mm:ss yyyy/MM/dd
     * 返回为可以符合DB2 timestamp格式的字符串
     * @param datestr
     * @return
     */
    public static String parseDateFormat(String datestr){
        String restr = null;
        try
        {
            restr = datestr==null||datestr.equals("")?"CURRENT TIMESTAMP":"TIMESTAMP('"+Formatter.db2_sdf.format(Formatter.sdf.parse(datestr))+"')";
        }catch(ParseException e)
        {
            e.printStackTrace();
        }
        return restr;
    }
    
    /**
     * 将制定的参数通过指定的PrintStream对象发生给client端
     * @param cmd
     * @param status
     * @param errorMessage
     * @param PS
     */
    public static void sendMessage(String cmd,int status,String errorMessage,PrintStream PS){
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("cmd", cmd);
        map.put("status", status);
        map.put("error", errorMessage);
        JSONObject json = JSONObject.fromObject(map);
        BusinessUtil.aLogger.info("开始返回json格式信息到client端..."+json.toString());
        PS.println(json.toString());
        PS.flush();
        BusinessUtil.aLogger.info("完成返回json格式信息到client端..."+json.toString());
    }
    
    //Send a message to all clients (utility routine)
    public static void sendToPND( String name,String message ) {
        boolean flag = true;
        
            for(Iterator<String> itr = MobileServer.outputStreams.keySet().iterator(); itr.hasNext();){
                String e_name = (String)itr.next();
                if (e_name.equals(name)){
                    // ... get the output stream ...
                    PrintStream dout = (PrintStream)MobileServer.outputStreams.get(e_name);
                    dout.println( message );
                    flag = false;
                }
            }
            if (flag){
                BusinessUtil.aLogger.info("无法为name为["+name+"]的PND发送指令讯息："+message);
            }
    }
    
    /**
     * 在hashtable中放入指定的的printstream object
     * @param uuid
     * @param PS
     */
    public static void addConnection(String uuid,PrintStream PS){
        if (MobileServer.outputStreams.containsKey(uuid)){
            MobileServer.outputStreams.remove(uuid);
        }
        MobileServer.outputStreams.put(uuid,PS);
        BusinessUtil.aLogger.info("放入了UUID为"+uuid+"的outputstream");
        
    }
    
    /**
     * 在hashtable中删除指定的printstream object
     * @param uuid
     */
    public static void removeConnection(String uuid) {
        synchronized(MobileServer.outputStreams) {
	        if (MobileServer.outputStreams.containsKey(uuid)){
	            // Remove it from our hashtable/list
	            MobileServer.outputStreams.remove(uuid);
	        }
        }
    }
    
    /**
     * 在healthCheckList中删除指定的healthcheck object
     * @param uuid
     */
    public static void removeHealthCheck(String uuid) {
        synchronized(MobileServer.outputStreams) {
	        if (MobileServer.healthCheckList.containsKey(uuid)){
	            // Remove it from our hashtable/list
	            MobileServer.healthCheckList.remove(uuid);
	        }
        }
    }
    
    /**
     * 把讯息同时打印在控制台和桌面程序中
     * @param Message
     */
    public static void printMessage(Object Message,LogTypeEnum lte){
    	//System.out.println(Message);
    	if (lte==LogTypeEnum.system) sLogger.info(Message);
    	if (lte==LogTypeEnum.application) aLogger.info(Message);
    	/*if (ControlForm.SystemMessageTextArea!=null){
    		ControlForm.SystemMessageTextArea.append((String)Message+"\12");
    		if (ControlForm.SystemMessageTextArea.getText().length()>10000){
    			ControlForm.SystemMessageTextArea.replaceRange("", 0, ((String)Message).length());
    		}
    		ControlForm.SystemMessageTextArea.setCaretPosition(ControlForm.SystemMessageTextArea.getText().length());
    	}*/
    }
    
    /**
     * 对统计lock wait count加1
     */
    public static synchronized void inc(){
    	MobileServer.KEY_LOCK_WAITING_COUNT++;
    }
    
    public static void main(String args[]) throws InterruptedException, InstantiationException, IllegalAccessException{
    	Date date = new Date();
    	System.out.println(getLastDayOfMonth(date));
    }
}
